﻿using FarPoint.Web.Spread;
using System;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.UI.WebControls;
using System.Xml;


namespace ControlExplorer.samples.GroupWare
{
  public partial class Overview : SpreadDemoPage
  {
    protected Int32[] skinArray;

    protected override bool ApplyGlobalSkin
    {
      get
      {
        return false;
      }
    }

    protected void Page_Load(object sender, System.EventArgs e)
    {
      this.FpSpread1.UpdateCommand += new FarPoint.Web.Spread.SpreadCommandEventHandler(this.FpSpread1_UpdateCommand);
      this.FpSpread1.ChildViewCreated += new FarPoint.Web.Spread.CreateChildViewEventHandler(this.FpSpread1_ChildViewCreated);

      if (this.IsPostBack)
      {
        // load array of user-selected skins from viewstate
        skinArray = (Int32[])ViewState["skinArray"];

        #region Old version of show total cost
        //// re-add the travel allowance summation code programmatically after each postback
        ////   since it is not part of the dataset (basically an unbound row)
        //// subtract 1 from rowcount since the row has already been added
        //int rowCount = FpSpread1.Sheets[FpSpread1.ActiveSheetViewIndex].Rows.Count - 1;
        //FpSpread1.Sheets[FpSpread1.ActiveSheetViewIndex].Cells[rowCount, 4].Formula = "SUM(E1:E" + rowCount.ToString() + ")";
        #endregion

        return;
      }

      // create array for user-selected skins
      skinArray = new Int32[3];
      this.FpSpread1.UseClipboard = false;

      // set the active sheet tab back color to be more prominent
      FpSpread1.Tab.ActiveTabBackColor = Color.LightCoral;
      // set the tab policy to always show tabs even when commandbar is made invisible
      FpSpread1.Tab.TabControlPolicy = FarPoint.Web.Spread.TabControlPolicy.Always;

      // clear any previous sheets
      FpSpread1.Sheets.Clear();

      // add one sheet each for research, marketing, and support divisions of company 
      SheetView sv = new SheetView();
      sv.SheetName = "调查";
      FpSpread1.Sheets.Add(sv);
      sv = new SheetView();
      sv.SheetName = "销售";
      FpSpread1.Sheets.Add(sv);
      sv = new SheetView();
      sv.SheetName = "支持";
      FpSpread1.Sheets.Add(sv);

      // set Spread to show hierarchical view
      FpSpread1.HierarchicalView = true;

      // set the default style to disable editing
      FpSpread1.Sheets[0].DefaultStyle.Locked = true;
      FpSpread1.Sheets[1].DefaultStyle.Locked = true;
      FpSpread1.Sheets[2].DefaultStyle.Locked = true;

      // initialize array of user-selected skins
      skinArray[0] = 0;
      skinArray[1] = 0;
      skinArray[2] = 0;

      // persist array of user-selected skins to viewstate
      ViewState["skinArray"] = skinArray;

      // first sheet - research
      // make sure row header is large enough to show hierarchy cues
      FpSpread1.Sheets[0].RowHeader.Width = 50;
      // set column wide enough to show long string data
      FpSpread1.Sheets[0].SetColumnWidth(3, 100);
      FpSpread1.Sheets[0].AllowSort = true;
      // sort by column 1 (last name)
      FpSpread1.Sheets[0].SortRows(1, true, false);
      FpSpread1.Sheets[0].AllowSort = false;


      // second sheet - marketing
      // make sure row header is large enough to show hierarchy cues
      FpSpread1.Sheets[1].RowHeader.Width = 50;
      // set column wide enough to show long string data
      FpSpread1.Sheets[1].SetColumnWidth(3, 100);
      FpSpread1.Sheets[1].AllowSort = true;
      // sort by column 1 (last name)
      FpSpread1.Sheets[1].SortRows(1, true, false);
      FpSpread1.Sheets[1].AllowSort = false;

      // third sheet - support
      // make sure row header is large enough to show hierarchy cues
      FpSpread1.Sheets[2].RowHeader.Width = 50;
      // set column wide enough to show long string data
      FpSpread1.Sheets[2].SetColumnWidth(3, 100);
      FpSpread1.Sheets[2].AllowSort = true;
      // sort by column 1 (last name)
      FpSpread1.Sheets[2].SortRows(1, true, false);
      FpSpread1.Sheets[2].AllowSort = false;


      // Loop through and display the default skin names
      foreach (FarPoint.Web.Spread.SheetSkin fpskin in FarPoint.Web.Spread.DefaultSkins.Skins)
      {
        SkinList.Items.Add(fpskin.Name);
      }


      // populate dataset
      // this.CreateDataSet();
      // save dataset to external XML file
      // SaveDataSetXML  mySavedXML = new SaveDataSetXML(this.Request.ApplicationPath, (DataSet)corpDataSet, "sample.xml", "sample.xsd");

      // resources path
      string resPath = this.Request.Path.Substring(0, this.Request.Path.LastIndexOf("/"));

      // load external XML file 
      LoadDataSetXML myLoadedXML = new LoadDataSetXML(resPath, "research.xml", "research.xsd");
      // set dataset created from external XML into Spread first sheet 
      FpSpread1.Sheets[0].DataSource = myLoadedXML.GetDataSet();
      // set "employees" table as main data table
      FpSpread1.Sheets[0].DataMember = "employees";

      // load external XML file 
      myLoadedXML = new LoadDataSetXML(resPath, "marketing.xml", "marketing.xsd");
      // set dataset created from external XML into Spread second sheet
      FpSpread1.Sheets[1].DataSource = myLoadedXML.GetDataSet();
      // set "employees" table as main data table
      FpSpread1.Sheets[1].DataMember = "employees";

      // load external XML file 
      myLoadedXML = new LoadDataSetXML(resPath, "support.xml", "support.xsd");
      // set dataset created from external XML into Spread third sheet
      FpSpread1.Sheets[2].DataSource = myLoadedXML.GetDataSet();
      // set "employees" table as main data table
      FpSpread1.Sheets[2].DataMember = "employees";

      // set the fourth column to currency cell type for 'allowance'
      FpSpread1.Sheets[0].Columns[4].CellType = new FarPoint.Web.Spread.CurrencyCellType(0.0M, 100000.00M);
      FpSpread1.Sheets[1].Columns[4].CellType = new FarPoint.Web.Spread.CurrencyCellType(0.0M, 100000.00M);
      FpSpread1.Sheets[2].Columns[4].CellType = new FarPoint.Web.Spread.CurrencyCellType(0.0M, 100000.00M);

      // set the first name column bold
      FpSpread1.Sheets[0].Columns[0].Font.Bold = true;
      FpSpread1.Sheets[1].Columns[0].Font.Bold = true;
      FpSpread1.Sheets[2].Columns[0].Font.Bold = true;

      // set the last name column bold
      FpSpread1.Sheets[0].Columns[1].Font.Bold = true;
      FpSpread1.Sheets[1].Columns[1].Font.Bold = true;
      FpSpread1.Sheets[2].Columns[1].Font.Bold = true;

      // align hire dates on right
      FpSpread1.Sheets[0].Columns[5].HorizontalAlign = HorizontalAlign.Right;
      FpSpread1.Sheets[1].Columns[5].HorizontalAlign = HorizontalAlign.Right;
      FpSpread1.Sheets[2].Columns[5].HorizontalAlign = HorizontalAlign.Right;

      // set custom columnheader text for first sheet
      FpSpread1.Sheets[0].ColumnHeader.Cells[0, 0].Text = "姓";
      FpSpread1.Sheets[0].ColumnHeader.Cells[0, 1].Text = "名";
      FpSpread1.Sheets[0].ColumnHeader.Cells[0, 2].Text = "员工ID";
      FpSpread1.Sheets[0].ColumnHeader.Cells[0, 3].Text = "电话号码";
      FpSpread1.Sheets[0].ColumnHeader.Cells[0, 4].Text = "出差补贴";
      FpSpread1.Sheets[0].ColumnHeader.Cells[0, 5].Text = "入职时间";

      // set custom columnheader text for second sheet
      FpSpread1.Sheets[1].ColumnHeader.Cells[0, 0].Text = "姓";
      FpSpread1.Sheets[1].ColumnHeader.Cells[0, 1].Text = "名";
      FpSpread1.Sheets[1].ColumnHeader.Cells[0, 2].Text = "员工ID";
      FpSpread1.Sheets[1].ColumnHeader.Cells[0, 3].Text = "电话号码";
      FpSpread1.Sheets[1].ColumnHeader.Cells[0, 4].Text = "出差补贴";
      FpSpread1.Sheets[1].ColumnHeader.Cells[0, 5].Text = "入职时间";

      // set custom columnheader text for third sheet
      FpSpread1.Sheets[2].ColumnHeader.Cells[0, 0].Text = "姓";
      FpSpread1.Sheets[2].ColumnHeader.Cells[0, 1].Text = "名";
      FpSpread1.Sheets[2].ColumnHeader.Cells[0, 2].Text = "员工ID";
      FpSpread1.Sheets[2].ColumnHeader.Cells[0, 3].Text = "电话号码";
      FpSpread1.Sheets[2].ColumnHeader.Cells[0, 4].Text = "出差补贴";
      FpSpread1.Sheets[2].ColumnHeader.Cells[0, 5].Text = "入职时间";

      // set column wide enough to show long columnheader text
      FpSpread1.Sheets[0].SetColumnWidth(4, 125);
      FpSpread1.Sheets[1].SetColumnWidth(4, 125);
      FpSpread1.Sheets[2].SetColumnWidth(4, 125);

      // set footer
      for (int i = 0; i < FpSpread1.Sheets.Count; i++)
      {
        // use column footer to sum up all allowances for a tatal cost
        FpSpread1.Sheets[i].ColumnFooter.Visible = true;
        // set aggregate type to column 4
        FpSpread1.Sheets[i].Columns[4].AggregationType = FarPoint.Web.Spread.Model.AggregationType.Sum;
        // set style to footer
        FpSpread1.Sheets[i].ColumnFooter.Cells[0, 4].Formatter = new CurrencyCellType();
        FpSpread1.Sheets[i].ColumnFooter.Cells[0, 4].HorizontalAlign = HorizontalAlign.Left;
        FpSpread1.Sheets[i].ColumnFooter.Cells[0, 4].ColumnSpan = 2;
        // set footer text to format value
        FpSpread1.Sheets[i].ColumnFooter.Cells[0, 4].Value = "出差补贴合计: <b>{0}</b>";
      }

      #region Old version of show total cost
      //int rCount = FpSpread1.Sheets[0].Rows.Count;
      //// first sheet
      //// add a row to sum up all allowances for a total cost
      ////    column 4 (zero-based) is the allowance column
      //FpSpread1.Sheets[0].AddRows(rCount, 1);
      //// set the cell type to my custom text celltype for a text label
      ////   see the custom class "MyTextCellType" at the bottom of this file
      //MyTextCellType myCell = new MyTextCellType();
      //// set the cell text
      //myCell.Text = "Travel Allowance Total:";
      //// assign the custom cell type instance into the FpSpread
      //FpSpread1.Sheets[0].Cells[rCount, 2].CellType = myCell;
      //// allow the text to span multiple cells
      //FpSpread1.Sheets[0].Cells[rCount, 2].ColumnSpan = 2;
      //// add the travel allowance summation code programmatically
      //FpSpread1.Sheets[0].Cells[rCount, 4].Formula = "SUM(E1:E" + rCount.ToString() + ")";
      //// second sheet 
      //rCount = FpSpread1.Sheets[1].Rows.Count;
      //// add a row to sum up all allowances for a total cost
      ////    column 4 (zero-based) is the allowance column
      //FpSpread1.Sheets[1].AddRows(rCount, 1);
      //// set the cell type to my custom text celltype for a text label
      ////   see the custom class "MyTextCellType" at the bottom of this file
      //MyTextCellType myCell2 = new MyTextCellType();
      //// set the cell text
      //myCell2.Text = "Travel Allowance Total:";
      //// assign the custom cell type instance into the FpSpread
      //FpSpread1.Sheets[1].Cells[rCount, 2].CellType = myCell2;
      //// allow the text to span multiple cells
      //FpSpread1.Sheets[1].Cells[rCount, 2].ColumnSpan = 2;
      //// add the travel allowance summation code programmatically
      //FpSpread1.Sheets[1].Cells[rCount, 4].Formula = "SUM(E1:E" + rCount.ToString() + ")";
      //// third sheet 
      //rCount = FpSpread1.Sheets[2].Rows.Count;
      //// add a row to sum up all allowances for a total cost
      ////    column 4 (zero-based) is the allowance column
      //FpSpread1.Sheets[2].AddRows(rCount, 1);
      //// set the cell type to my custom text celltype for a text label
      ////   see the custom class "MyTextCellType" at the bottom of this file
      //MyTextCellType myCell3 = new MyTextCellType();
      //// set the cell text
      //myCell3.Text = "Travel Allowance Total:";
      //// assign the custom cell type instance into the FpSpread
      //FpSpread1.Sheets[2].Cells[rCount, 2].CellType = myCell3;
      //// allow the text to span multiple cells
      //FpSpread1.Sheets[2].Cells[rCount, 2].ColumnSpan = 2;
      //// add the travel allowance summation code programmatically
      //FpSpread1.Sheets[2].Cells[rCount, 4].Formula = "SUM(E1:E" + rCount.ToString() + ")";
      #endregion

      // set up lower FpSpread to represent corporate logo image as Image celltype
      // since logo is .gif, backcolor will show through transparent areas
      FpSpread2.Sheets[0].BackColor = Color.SlateBlue;
      // hide headers
      FpSpread2.ColumnHeader.Visible = false;
      FpSpread2.RowHeader.Visible = false;
      FpSpread2.Sheets[0].ColumnCount = 1;
      FpSpread2.Sheets[0].RowCount = 1;
      // set cell (0,0) to have dimensions of image 
      FpSpread2.Sheets[0].Columns[0].Width = (int)FpSpread2.Width.Value - 2;
      FpSpread2.Sheets[0].Rows[0].Height = (int)FpSpread2.Height.Value - 2;
      // hide command bar
      FpSpread2.CommandBar.Visible = false;
      // set celltype with image to be loaded
      FpSpread2.Cells[0, 0].CellType = new FarPoint.Web.Spread.ImageCellType(resPath + "/title.gif");

      // set title of spread
      FpSpread1.TitleInfo.Visible = true;
      FpSpread1.TitleInfo.BackColor = Color.SlateBlue;
      ImageCellType ict = new ImageCellType(resPath + "/title.gif");
      FpSpread1.TitleInfo.CellType = ict;
      FpSpread1.TitleInfo.Text = " Currently Showing Schedules";
      FpSpread1.TitleInfo.HorizontalAlign = HorizontalAlign.Left;
      FpSpread1.TitleInfo.Font.Size = FontUnit.Medium;
      FpSpread1.TitleInfo.Font.Bold = true;
      FpSpread1.TitleInfo.ForeColor = Color.Gold;
      FpSpread1.TitleInfo.Border = new Border(BorderStyle.Outset, Color.White, 3);
      FpSpread1.TitleInfo.Height = 100;
    }

    private void SetChildViewStyle(SheetView sv)
    {
      // set child view to disable editing
      sv.DefaultStyle.Locked = true;

      if (sv.RelationName == "schedules")
      {
        sv.BackColor = Color.Tan;
        sv.RowHeader.Visible = false;
        if ((DropDownList1.SelectedIndex == 0) || (DropDownList1.SelectedIndex == 1)) // schedules
        {
          if (DropDownList1.SelectedIndex == 0)  // no column headers
            sv.ColumnHeader.Visible = false;
          else                                   // column headers
            sv.ColumnHeader.Visible = true;

          FpSpread1.TitleInfo.Text = " Currently showing employee schedules";
          sv.AlternatingRows[0].ForeColor = Color.Red;
          sv.AlternatingRows[1].ForeColor = Color.Red;
          sv.AlternatingRows[0].BackColor = Color.BlanchedAlmond;
          sv.AlternatingRows[1].BackColor = Color.BlanchedAlmond;
          // expand description column to show long text
          sv.SetColumnWidth(0, 150);
          // hide employee id field that maps relationship
          sv.SetColumnVisible(2, false);
          // set row height back to default height
          sv.DefaultRowHeight = FpSpread1.Sheets[FpSpread1.ActiveSheetViewIndex].DefaultRowHeight;
          sv.AllowSort = true;
          // sort by event date
          sv.SortRows(1, true, false);
          sv.AllowSort = false;
          // align event dates on right
          sv.Columns[1].HorizontalAlign = HorizontalAlign.Right;
          sv.Visible = true;
        }
        else
        {
          // if schedules are hidden, set row height = 0 to hide them
          sv.ColumnHeader.Visible = false;
          sv.DefaultRowHeight = 0;
          sv.Visible = false;
        }
      }
      else if (sv.RelationName == "groupMembers")
      {
        sv.BackColor = Color.Tan;
        sv.RowHeader.Visible = false;
        if ((DropDownList1.SelectedIndex == 2) || (DropDownList1.SelectedIndex == 3)) // team members
        {
          if (DropDownList1.SelectedIndex == 2)  // no column headers
            sv.ColumnHeader.Visible = false;
          else                                   // column headers
            sv.ColumnHeader.Visible = true;

          FpSpread1.TitleInfo.Text = " Currently showing project team members";
          sv.AlternatingRows[0].ForeColor = Color.DarkGreen;
          sv.AlternatingRows[1].ForeColor = Color.DarkGreen;
          sv.AlternatingRows[0].BackColor = Color.Khaki;
          sv.AlternatingRows[1].BackColor = Color.Khaki;

          // hide employee id field that maps relationship
          sv.SetColumnVisible(3, false);
          // set row height back to default height
          sv.DefaultRowHeight = FpSpread1.Sheets[FpSpread1.ActiveSheetViewIndex].DefaultRowHeight;
          // sort by last name
          sv.SortRows(2, true, false);
          sv.AllowSort = false;
          sv.Visible = true;
        }
        else
        {
          // if team members are hidden, set row height = 0 to hide them
          sv.ColumnHeader.Visible = false;
          sv.DefaultRowHeight = 0;
          sv.Visible = false;
        }
      }
    }

    private void FpSpread1_ChildViewCreated(object sender, CreateChildViewEventArgs e)
    {
      SetChildViewStyle(e.SheetView);
    }

    private void FpSpread1_UpdateCommand(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)
    {
      SheetView sh = e.SheetView;
      string n = sh.SheetName;
    }

    // this function creates a company division dataset, i.e. marketing or support, 
    //   of default data with relationships
    private void CreateDataSet()
    {
      DataTable employees;
      DataTable schedules;
      DataTable groupMembers;

      // create new dataset
      corpDataSet = new DataSet();

      // add main employees table (group leaders or managers)
      employees = corpDataSet.Tables.Add("employees");
      // add columns
      employees.Columns.AddRange(new DataColumn[] {new DataColumn("first_name", typeof(string)), 
                                                    new DataColumn("last_name", typeof(string)), 
                                                    new DataColumn("employee_id", typeof(int)), 
                                                    new DataColumn("contact_no", typeof(string)),
                                                    new DataColumn("allowance", typeof(double)),
                                                    new DataColumn("hire_date", typeof(DateTime)) });
      // add main employees data
      employees.Rows.Add(new object[] { "Joe", "Alexander", 33040, "919.555.1212", 500.00, new DateTime(2001, 2, 1) });
      employees.Rows.Add(new object[] { "Susan", "Davidson", 11274, "919.555.1212", 500.00, new DateTime(2000, 7, 22) });
      employees.Rows.Add(new object[] { "Juan", "Lopez", 44523, "919.555.1212", 500.00, new DateTime(2002, 1, 17) });
      employees.Rows.Add(new object[] { "Rita", "Polowski", 34333, "919.555.1212", 500.00, new DateTime(2001, 6, 15) });
      employees.Rows.Add(new object[] { "Brad", "Wong", 10001, "919.555.1212", 500.00, new DateTime(1999, 9, 12) });

      // add secondary table of schedules
      schedules = corpDataSet.Tables.Add("schedules");
      // add columns
      schedules.Columns.AddRange(new DataColumn[] { new DataColumn("description", typeof(string)), new DataColumn("date", typeof(DateTime)), new DataColumn("employee_id", typeof(int)) });

      // add schedules data
      schedules.Rows.Add(new object[] { "Cleveland Trip", new DateTime(2002, 11, 17), 44523 });
      schedules.Rows.Add(new object[] { "Cleveland Trip", new DateTime(2002, 11, 17), 11274 });

      schedules.Rows.Add(new object[] { "Vacation", new DateTime(2002, 12, 17), 44523 });
      schedules.Rows.Add(new object[] { "Vacation", new DateTime(2003, 1, 12), 34333 });
      schedules.Rows.Add(new object[] { "Vacation", new DateTime(2003, 3, 17), 11274 });

      schedules.Rows.Add(new object[] { "Personal Day", new DateTime(2003, 1, 2), 10001 });
      schedules.Rows.Add(new object[] { "Personal Day", new DateTime(2002, 12, 22), 33040 });

      schedules.Rows.Add(new object[] { "Spread Demo", new DateTime(2002, 11, 4), 34333 });

      // add team members table (each associated with an employee at the top level or manager)
      groupMembers = corpDataSet.Tables.Add("groupMembers");
      // add columns
      groupMembers.Columns.AddRange(new DataColumn[] { new DataColumn("full_time", typeof(bool)), new DataColumn("first_name", typeof(string)), new DataColumn("last_name", typeof(string)), new DataColumn("lead_id", typeof(int)) });

      // add team member data
      groupMembers.Rows.Add(new object[] { true, "Mark", "Thompson", 44523 });
      groupMembers.Rows.Add(new object[] { true, "Cecilia", "Roberts", 44523 });
      groupMembers.Rows.Add(new object[] { true, "Jennifer", "Moore", 44523 });

      groupMembers.Rows.Add(new object[] { true, "Jeanne", "Grainger", 34333 });
      groupMembers.Rows.Add(new object[] { false, "Bill", "Townsend", 34333 });
      groupMembers.Rows.Add(new object[] { false, "Frank", "Lloyd", 34333 });
      groupMembers.Rows.Add(new object[] { false, "Pete", "Wikedale", 34333 });

      groupMembers.Rows.Add(new object[] { true, "Albert", "Roos", 11274 });
      groupMembers.Rows.Add(new object[] { true, "Chastity", "Beals", 11274 });
      groupMembers.Rows.Add(new object[] { true, "Heather", "Jones", 11274 });


      // finally add the relationships between schedules and team members to the top level employees or managers
      corpDataSet.Relations.Add("schedules", employees.Columns["employee_id"], schedules.Columns["employee_id"]);
      corpDataSet.Relations.Add("groupMembers", employees.Columns["employee_id"], groupMembers.Columns["lead_id"]);

    }


    #region Web Form Designer generated code
    override protected void OnInit(EventArgs e)
    {
      //
      // CODEGEN: This call is required by the ASP.NET Web Form Designer.
      //
      InitializeComponent();
      base.OnInit(e);
    }

    /// <summary>
    /// Required method for Designer support - do not modify
    /// the contents of this method with the code editor.
    /// </summary>
    private void InitializeComponent()
    {
      this.FpSpread1.UpdateCommand += new FarPoint.Web.Spread.SpreadCommandEventHandler(this.FpSpread1_UpdateCommand);
      this.FpSpread1.ChildViewCreated += new FarPoint.Web.Spread.CreateChildViewEventHandler(this.FpSpread1_ChildViewCreated);

    }
    #endregion

    private System.Data.DataSet corpDataSet;

    protected void FpSpread1_ActiveSheetChanged(object sender, System.EventArgs e)
    {
      // set the item in the skin drop down list when the sheet changes
      SkinList.SelectedIndex = skinArray[FpSpread1.ActiveSheetViewIndex];
    }

    protected void HierBarCheckBox_CheckedChanged(object sender, System.EventArgs e)
    {
      // autopostback will update the Spread
      FpSpread1.HierarchicalView = !(HierBarCheckBox.Checked);
    }

    protected void DropDownList1_SelectedIndexChanged(object sender, System.EventArgs e)
    {
      // autopostback will update the Spread
      if ((DropDownList1.SelectedIndex == 0) || (DropDownList1.SelectedIndex == 1)) // schedules
        FpSpread1.TitleInfo.Text = " Currently showing schedules";
      else
        FpSpread1.TitleInfo.Text = " Currently showing project team members";  // team members    
    }

    protected void SkinList_SelectedIndexChanged(object sender, System.EventArgs e)
    {
      // autopostback will update the Spread
      // set the skin for the current sheet of the Spread
      FarPoint.Web.Spread.DefaultSkins.GetAt(SkinList.SelectedIndex).Apply(FpSpread1.Sheets[FpSpread1.ActiveSheetViewIndex]);
      // save into array the selected skin index
      skinArray[FpSpread1.ActiveSheetViewIndex] = SkinList.SelectedIndex;
      // persist array of user-selected skins to viewstate
      ViewState["skinArray"] = skinArray;

    }

    protected void ExpandBtn_Click(object sender, System.EventArgs e)
    {
      // programmatically expand rows, in this case all rows
      for (int i = 0; i < FpSpread1.Sheets[FpSpread1.ActiveSheetViewIndex].RowCount; i++)
        FpSpread1.Sheets[FpSpread1.ActiveSheetViewIndex].ExpandRow(i, true);
    }

    protected void CollapseBtn_Click(object sender, System.EventArgs e)
    {
      // programmatically collapse rows, in this case all rows
      for (int i = 0; i < FpSpread1.Sheets[FpSpread1.ActiveSheetViewIndex].RowCount; i++)
        FpSpread1.Sheets[FpSpread1.ActiveSheetViewIndex].ExpandRow(i, false);

    }

    protected void CmdBarHide_CheckedChanged(object sender, System.EventArgs e)
    {
      FpSpread1.CommandBar.Visible = !(CmdBarHide.Checked);
    }

    protected void ExportXML_Click(object sender, System.EventArgs e)
    {
      // map path to IIS server directory
      String pathname = HttpContext.Current.Server.MapPath(this.Request.ApplicationPath + "//");
      // save FpSpread1 to XML with data and attributes, change second parameter to true for DataOnly
      FpSpread1.Save(pathname + "exportedSpread.xml", false);
      Label5.Visible = true;
    }

    protected void SubmitPassword_Click(object sender, System.EventArgs e)
    {
      if (PasswordBox.Text == "管理员")
      {
        // clear password textbox
        PasswordBox.Text = "";
        // set status label text and forecolor
        Label7.ForeColor = Color.Blue;
        Label7.Text = "单元格已解锁";
        // set the default style to enable editing
        FpSpread1.Sheets[0].DefaultStyle.Locked = false;
        FpSpread1.Sheets[1].DefaultStyle.Locked = false;
        FpSpread1.Sheets[2].DefaultStyle.Locked = false;
      }
    }
  }


  // this is a custom derived celltype class: it allows Text to be displayed without setting
  // the underlying value into the cell, i.e. can place a text cell in a formatted (numeric, etc.) column

  /// <summary>
  /// Summary description for GroupWare.
  /// </summary>
  /// 

  [Serializable]
  public class MyTextCellType : FarPoint.Web.Spread.GeneralCellType
  {
    // the text string to display
    private String myString = "";

    // write out our one and only property
    public override bool Serialize(XmlTextWriter w)
    {
      w.WriteString(myString);
      return true;
    }
    // read in and assign our one and only property
    public override bool Deserialize(XmlNodeReader r)
    {
      myString = r.ReadString();
      return true;
    }
    // when parsing is necessary, return null as if cell is empty
    public override object Parse(String s)
    {
      return null;
    }
    // when format is called, just return our string property
    public override String Format(object o)
    {
      return myString;
    }
    // the one and only property, the text string to display
    public virtual String Text
    {
      get { return myString; }
      set { myString = value; }
    }
  }
}